CREATE CAST
CREATE CAST — Define a new cast
Synopsis
CREATE CAST (source_type AS target_type)
WITH FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITH INOUT
[ AS ASSIGNMENT | AS IMPLICIT ]
Description
CREATE CAST defines a new cast. A cast specifies how to perform a conversion between two data types. For example:
SELECT CAST(42 AS float8);
converts the integer constant 42 to type float8 by calling a previously specified function (in this case, float8(int4)). If no suitable cast is defined, the conversion fails.
Two types can be binary-coercible, which means the conversion can be performed "for free" without calling any function.
This requires the corresponding values to use the same internal representation. For example, the types text and varchar are binary-coercible in both directions. Binary coercibility is not necessarily a symmetric relation. For example, in the current implementation, casting from xml to text can be performed for free, but the reverse requires a function to perform at least a syntax check (two types that are binary-coercible in both directions are also called binary-compatible).
By using the WITH INOUT syntax, you can define a cast as an I/O conversion cast. An I/O conversion cast is performed by calling the source data type's output function and passing the resulting string to the target data type's input function.
In many common situations, this feature avoids the need to define a separate cast function. An I/O conversion cast behaves the same as a regular function-based cast, only differing in implementation.
By default, a cast is only invoked upon an explicit cast request, in the form CAST(x AS typename) or x::typename.
If the cast is marked AS ASSIGNMENT, it will be implicitly invoked when assigning a value to a column of the target data type. For example, suppose foo.f1 is a column of type text, then if the cast from type integer to type text is marked as AS ASSIGNMENT, the following:
INSERT INTO foo (f1) VALUES (42);
will be allowed; otherwise, it will not be allowed (we typically use the term assignment cast to describe such casts).
If the cast is marked AS IMPLICIT, it can be implicitly invoked in any context, whether in assignment or inside an expression (we typically use the term implicit cast to describe such casts). For example, consider this query:
SELECT 2 + 4.0;
The parser initially marks the constants as type integer and numeric, respectively. There is no integer + numeric operator in the system catalog, but there is a numeric + numeric operator. Therefore, if there is an available cast from integer to numeric that is marked AS IMPLICIT — and indeed there is — the query will succeed.
The parser will apply the implicit conversion and resolve the query as if it had been written:
SELECT CAST ( 2 AS numeric ) + 4.0;
The system also supports an implicit cast from numeric to integer by default. If this cast were marked AS IMPLICIT — which it is not — then the parser would face a choice: use the process described above, or cast the numeric constant to integer and apply the integer + integer operator. Lacking a way to determine which choice is better, the parser would give up and report that the query is ambiguous.
It is wise to be conservative with implicit casts. Too many implicit casts can cause the system to interpret commands in inexplicable ways, or to fail to parse commands altogether due to multiple possible interpretations. A good practice is to make a cast implicitly invocable only for information-preserving conversions between types in the same type category. For example,
the cast from int2 to int4 can reasonably be marked as implicit, but the cast from float8 to int4 should probably only be usable on assignment.
[TABLE]
To create a cast, you must own the source and target data types and have USAGE privilege on the other type. To create a binary-coercible cast, you must be a superuser (this restriction exists because an erroneous binary-coercible cast can easily crash the server).
Parameters
source_type
The name of the source data type of the cast.
target_type
The name of the target data type of the cast.
function_name[(argument_type [, ...])]
The function used to perform the cast. The function name can be schema-qualified. If not qualified, the function will be looked up in the schema search path. The function's result data type must be the target data type of the cast. Its arguments are discussed below.
If no argument list is specified, the function name must be unique in its schema.
WITHOUT FUNCTION
Indicates that the source type can be binary-coerced to the target type, so no function is required to execute the cast.
WITH INOUT
Indicates that the cast is an I/O conversion cast, executed by calling the source data type's output function and passing the resulting string to the target data type's input function.
AS ASSIGNMENT
Indicates that the cast can be implicitly invoked in assignment contexts.
AS IMPLICIT
Indicates that the cast can be implicitly invoked in any context.
The cast implementation function can have 1 to 3 arguments. The first argument type must be identical to the source type or binary-coercible from the source type. The second argument (if present) must be of type integer; it receives the type modifier associated with the target type, or -1 if there is no type modifier. The third argument (if present) must be of type boolean; it receives true if the cast is an explicit cast, or false otherwise (curiously, the SQL standard requires different behavior for explicit and implicit casts in some cases. This parameter is provided for functions that must implement such casts. It is not recommended for use when designing your own data types).
The return type of a cast function must be identical to the target type or binary-coercible to the target type.
Typically, a cast must have different source and target data types. However, if it has a cast implementation function with multiple arguments, it can be declared with the same source and target type. This is used to represent type-specific length coercion functions in the system catalog. The named function is used to coerce a value of the type to the type modifier value provided by its second argument.
When a cast has different source and target types and a function with multiple arguments, it supports converting from one type to another and applying length coercion in a single step. Without such an entry, casting to a type that uses type modifiers would involve two cast steps: one to convert between data types and another to apply the modifier.
Casting to or from a domain type is currently not effective. Casting to or from a domain uses the casts associated with its base type.
Notes
Use DROP CAST to remove user-defined casts.
Remember that if you want to be able to convert types in both directions, you need to explicitly declare casts in both directions.
It is usually not necessary to create casts between user-defined types and standard string types (text, varchar, and char(n), as well as user-defined types defined in the string category). The system provides automatic I/O conversion casts for them. Automatic casts to string types are treated as assignment casts, while automatic casts from string types are only explicit. You can override this behavior by declaring your own casts, but the only reason to do so is if you want the conversion to be more easily callable than the standard setup. Another possible reason is if you want the conversion to behave differently from the type's I/O functions, but this is surprising enough that you should think twice about whether it is a good idea (there are indeed a small number of built-in types whose casts behave differently, mostly due to requirements of the SQL standard).
While not necessary, it is recommended that you follow the convention of naming the cast implementation function after the target data type. Many users are accustomed to being able to use a function-style notation to cast data types, i.e., typename(x). This notation is exactly a call to the cast implementation function, which is not treated specially as a cast here. If your conversion function is not specified to support this convention, your users will be surprised. Since the system allows overloading the same function name with different parameter types, having multiple cast functions with the same name from different types to the same target type is not difficult.
Examples
# To create an assignment cast from type bigint to type int4 using the function int4(bigint):
CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;
# (This cast is already predefined in the system.)